package net.sky.report.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import net.sky.report.bean.BasSchool;
import net.sky.report.bean.BasSubject;
import net.sky.report.bean.BasTerm;
import net.sky.report.bean.SumTimes;
import net.sky.report.bean.TeaId;
import net.sky.report.bean.TimeTable;


public class TimeTableDao extends Dao{
	
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	/**
	 * teaName select teaId
	 * @param teaName
	 * @return
	 */
	public TeaId searchByteaName(String teaName){
		TeaId tu=null;
		StringBuffer sql=new StringBuffer("select * from bas_teacher bt where bt.tea_name='"+ teaName +"'");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				tu=new TeaId();
				tu.setTeaId(rs.getInt("tea_id"));
				tu.setTeaName(rs.getString("tea_name"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return tu;
	}
	/**
	 * schId,terId,subId select SUM
	 * @param schId
	 * @param terId
	 * @param subId
	 * @return
	 */
	public SumTimes getSum(int schSeq,int terId,int subId){
		SumTimes st=null;
		try{
			StringBuffer sql=new StringBuffer("select sum(bc.cla_prepcount) bc,sum(count(s.clh_stu_id)) sc from stu_classhistory s ,bas_school bs,bas_grade bg,bas_subject bu,bas_class bc,bas_teacher bt " +
					"where s.clh_insch_id=bs.sch_id and s.clh_gra_id=bg.gra_id and s.clh_sub_id=bu.sub_id and s.clh_incla_id=bc.cla_id and s.clh_tea_id=bt.tea_id and s.clh_onstatus=1 ");
			if(schSeq!=0){
				sql.append("and bs.sch_seq='"+ schSeq +"' ");
			}
			if(terId!=0){
				sql.append("and s.clh_ter_id="+ terId +" ");
			}
			if(subId!=0){
				sql.append("and bu.sub_id="+ subId +" ");
			}
			sql.append("group by bu.sub_name,bt.tea_name,bs.sch_name,bg.gra_name,bc.cla_name,bc.cla_schedule,bc.cla_prepcount,bc.cla_count,s.clh_ter_id");
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				st=new SumTimes();
				st.setSumPrepcount(rs.getInt("bc"));
				st.setSumClhstuid(rs.getInt("sc"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return st;
	}
	/**
	 * teaName select ALLdata
	 * @param teaName
	 * @return
	 */
	public SumTimes getSumByteaName(String teaName){
		SumTimes st=null;
		try{
			StringBuffer sql=new StringBuffer("select sum(bc.cla_prepcount) bc,sum(count(s.clh_stu_id)) sc from stu_classhistory s ,bas_school bs,bas_grade bg,bas_subject bu,bas_class bc,bas_teacher bt " +
					"where s.clh_insch_id=bs.sch_id and s.clh_gra_id=bg.gra_id and s.clh_sub_id=bu.sub_id and s.clh_incla_id=bc.cla_id and s.clh_tea_id=bt.tea_id and s.clh_ter_id=126 and s.clh_onstatus=1 ");
			
			if(teaName != null && !"".equals(teaName)){
				sql.append("and bt.tea_name like '%"+ teaName +"%' ");
			}
			
			sql.append("group by bu.sub_name,bt.tea_name,bs.sch_name,bg.gra_name,bc.cla_name,bc.cla_schedule,bc.cla_prepcount,bc.cla_count,s.clh_ter_id");
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				st=new SumTimes();
				st.setSumPrepcount(rs.getInt("bc"));
				st.setSumClhstuid(rs.getInt("sc"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return st;
	}
	/**
	 * show data
	 * @param schId,terId,teaName
	 * @return
	 */
	public List<TimeTable> searchAll(int schSeq,int terId,int subId){
		List<TimeTable> list =new ArrayList<TimeTable>();
		TimeTable tt=null;
		StringBuffer sql=new StringBuffer("select bt.tea_name,bs.sch_name,bu.sub_name,bg.gra_name,bc.cla_name,bc.cla_schedule,bc.cla_prepcount,count(s.clh_stu_id) cc,bc.cla_count,s.clh_ter_id " +
				"from stu_classhistory s ,bas_school bs,bas_grade bg,bas_subject bu,bas_class bc,bas_teacher bt " +
				"where s.clh_insch_id=bs.sch_id and s.clh_gra_id=bg.gra_id " +
				"and s.clh_sub_id=bu.sub_id and s.clh_incla_id=bc.cla_id " +
				"and s.clh_tea_id=bt.tea_id and s.clh_onstatus=1 ");
		if(schSeq!=0){
			sql.append("and bs.sch_seq="+ schSeq +" ");
		}
		if(terId!=0){
			sql.append("and s.clh_ter_id="+ terId +" ");
		}
		if(subId != 0){
			sql.append("and bu.sub_id="+ subId +" ");
		}
		sql.append("group by bu.sub_name,bt.tea_name,bs.sch_name,bg.gra_name,bc.cla_name,bc.cla_schedule,bc.cla_prepcount,bc.cla_count,s.clh_ter_id " +
				"order by bt.tea_name,bs.sch_name,bg.gra_name");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				tt=new TimeTable();
				tt.setTeaName(rs.getString("tea_name"));
				tt.setSchName(rs.getString("sch_name"));
				tt.setSubName(rs.getString("sub_name"));
				tt.setGraName(rs.getString("gra_name"));
				tt.setClaName(rs.getString("cla_name"));
				tt.setClaschEdule(rs.getString("cla_schedule"));
				tt.setClaCount(rs.getInt("cla_count"));
				tt.setClaPrepcount(rs.getInt("cla_prepcount"));
				tt.setStuNum(rs.getInt("cc"));
				tt.setTermId(rs.getInt("clh_ter_id"));
				list.add(tt);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * show data
	 * @param teaName
	 * @return
	 */
	public List<TimeTable> searchAllByteaName(String teaName){
		List<TimeTable> list =new ArrayList<TimeTable>();
		TimeTable tt=null;
		StringBuffer sql=new StringBuffer("select bt.tea_name,bs.sch_name,bu.sub_name,bg.gra_name,bc.cla_name,bc.cla_schedule,bc.cla_prepcount,count(s.clh_stu_id) cc,bc.cla_count,s.clh_ter_id " +
				"from stu_classhistory s ,bas_school bs,bas_grade bg,bas_subject bu,bas_class bc,bas_teacher bt " +
				"where s.clh_insch_id=bs.sch_id and s.clh_gra_id=bg.gra_id " +
				"and s.clh_sub_id=bu.sub_id and s.clh_incla_id=bc.cla_id " +
				"and s.clh_tea_id=bt.tea_id and s.clh_ter_id=126 and s.clh_onstatus=1 ");
		if(teaName != null && !"".equals(teaName)){
			sql.append("and bt.tea_name like '%"+ teaName +"%' ");
		}
		
		sql.append("group by bu.sub_name,bt.tea_name,bs.sch_name,bg.gra_name,bc.cla_name,bc.cla_schedule,bc.cla_prepcount,bc.cla_count,s.clh_ter_id");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				tt=new TimeTable();
				tt.setTeaName(rs.getString("tea_name"));
				tt.setSchName(rs.getString("sch_name"));
				tt.setSubName(rs.getString("sub_name"));
				tt.setGraName(rs.getString("gra_name"));
				tt.setClaName(rs.getString("cla_name"));
				tt.setClaschEdule(rs.getString("cla_schedule"));
				tt.setClaCount(rs.getInt("cla_count"));
				tt.setClaPrepcount(rs.getInt("cla_prepcount"));
				tt.setStuNum(rs.getInt("cc"));
				tt.setTermId(rs.getInt("clh_ter_id"));
				list.add(tt);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * show all school
	 * @return select * from bas_school where sch_id not in (188,147) order by sch_seq
	 */
	public List<BasSchool> searchSchool(){
		List<BasSchool> list =new ArrayList<BasSchool>();
		BasSchool bs=null;
		StringBuffer sql=new StringBuffer("select sch_desc,sch_seq from bas_school where sch_id not in (188,147) group by sch_desc,sch_seq order by sch_seq");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				bs=new BasSchool();
				//bs.setSchId(rs.getInt("sch_id"));
				bs.setSchName(rs.getString("sch_desc"));
				bs.setSchSeq(rs.getInt("sch_seq"));
				list.add(bs);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * show all school
	 * @return select * from bas_subject order by sub_id;
	 */
	public List<BasTerm> searchTerm(){
		List<BasTerm> list =new ArrayList<BasTerm>();
		BasTerm bs=null;
		StringBuffer sql=new StringBuffer("select * from bas_term b where b.ter_id in (125,126) order by b.ter_id desc");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				bs=new BasTerm();
				bs.setTerId(rs.getInt("ter_id"));
				bs.setTerName(rs.getString("ter_name"));
				list.add(bs);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * show all subject
	 * @return 
	 */
	public List<BasSubject> searchSubject(){
		List<BasSubject> list =new ArrayList<BasSubject>();
		BasSubject bs=null;
		StringBuffer sql=new StringBuffer("select * from bas_subject order by sub_id");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				bs=new BasSubject();
				bs.setSubId(rs.getInt("sub_id"));
				bs.setSubName(rs.getString("sub_name"));
				list.add(bs);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
}
